﻿using System;
using System.Data.SqlClient;
using System.Data;

namespace ATM
{
    class ATM
    {
        static SqlConnection OpenDatabaseConnection(string database)
        {
            string connectionString = @"Server=.\SQL_SERVER_2008;" +
            "Database = " + database + "; Integrated Security = true";
            SqlConnection connection = new SqlConnection(connectionString);

            return connection;
        }

        static void Widthdraw(SqlConnection connection, decimal sumToWithdraw, string cardNumber, string cardPin)
        {
            SqlDataReader reader = null;
            SqlTransaction withdrawTran =
            connection.BeginTransaction(IsolationLevel.RepeatableRead);
            if (IsCardNumberAndPinValid(connection, withdrawTran, cardNumber, cardPin,out reader) == false)
            {
                withdrawTran.Rollback();
                throw new ArgumentException("The card number does not exists or does not match the card pin.");
            }
            using (reader)
            {
                if (IsThereEnoughCash(reader, sumToWithdraw) == false)
                {
                    reader.Close();
                    withdrawTran.Rollback();
                    throw new ArgumentException("The sum you are trying to withdraw is greater than the available sum.");
                }
            }
                UpdateAccountSum(connection, withdrawTran, cardNumber, sumToWithdraw);
            
            withdrawTran.Commit();
            SaveLog(connection, cardNumber, sumToWithdraw);
            Console.WriteLine("Withdraw Successfull");
        }

        static void SaveLog(SqlConnection connection, string cardNumber, decimal amount)
        {
            string insertIntoLog = @"INSERT INTO AccountTransactions(CardNumber,TransactionDate,Amount)
                                    VALUES(@cardNumber,@transactionDate,@amount)";
            DateTime transactionDate = DateTime.Now;
            
            SqlCommand insertIntoLogCommand = new SqlCommand(insertIntoLog, connection);
            SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted);
            insertIntoLogCommand.Parameters.Add(new SqlParameter("@cardNumber", cardNumber));
            insertIntoLogCommand.Parameters.Add(new SqlParameter("@transactionDate", transactionDate));
            insertIntoLogCommand.Parameters.Add(new SqlParameter("@amount", amount));
            insertIntoLogCommand.Transaction = transaction;
            insertIntoLogCommand.ExecuteNonQuery();
            transaction.Commit();
        }

        static bool IsCardNumberAndPinValid(SqlConnection connection, SqlTransaction tran, string cardNumber, string cardPin, out SqlDataReader reader)
        {
            string getCard = @"SELECT * FROM CardAccounts
                               WHERE CardNumber = @cardNumber";
            SqlCommand getCardCommand = new SqlCommand(getCard, connection);
            getCardCommand.Parameters.Add(new SqlParameter("@cardNumber", cardNumber));
            getCardCommand.Transaction = tran;
            reader = getCardCommand.ExecuteReader();

            if (reader.Read() == false)
            {
                return false;
            }
            if (((string)reader["CardPin"]).Equals(cardPin)==false)
            {
                return false;
            }

            return true;
        }
//        static bool IsCardNumberAndPinValid(SqlConnection connection, SqlTransaction tran,string cardNumber, string cardPin)
//        {
//            string dropFunction = @"IF OBJECT_ID (N'dbo.CheckCardNumberAndPin', N'FN') IS NOT NULL
//                                    DROP FUNCTION dbo.CheckCardNumberAndPin;";
//            SqlCommand dropFunctionCommand = new SqlCommand(dropFunction, connection);
//            dropFunctionCommand.Transaction = tran;
//            dropFunctionCommand.ExecuteNonQuery();

//            string createCheckFunc = @"
//                                    CREATE FUNCTION dbo.[CheckCardNumberAndPin](@cardNumber char(10), @cardPin char(4))
//                                    RETURNS bit
//                                    BEGIN
//	                                    DECLARE @foundRow TABLE
//	                                    (
//	                                    CardNumber char(10),
//	                                    CardPin char(4),
//	                                    CardCash money
//	                                    )
//	
//	                                    INSERT INTO @foundRow(CardNumber, CardPin , CardCash)
//	                                    SELECT CardAccounts.CardNumber,CardAccounts.CardPin,CardAccounts.CardCash
//	                                    FROM CardAccounts
//	                                    WHERE CardAccounts.CardNumber = @cardNumber
//	
//	                                    IF((SELECT COUNT(*) FROM @foundRow)=0)
//	                                    BEGIN
//		                                    RETURN 0
//	                                    END
//	
//	                                    DECLARE @foundPin char(4)= (SELECT CardPin FROM @foundRow)
//	                                    IF(@foundPin = @cardPin)
//	                                    BEGIN
//		                                    RETURN 1
//	                                    END
//	
//	                                    RETURN 0
//                                    END";
//            SqlCommand createFunctionForCheking = new SqlCommand(createCheckFunc, connection);
//            createFunctionForCheking.Transaction = tran;
//            createFunctionForCheking.ExecuteNonQuery();

//            string checkCardAndPin = "SELECT dbo.CheckCardNumberAndPin(@cardNumber,@cardPin)";
//            SqlCommand checkCardAndPinMatching = new SqlCommand(checkCardAndPin, connection);
//            checkCardAndPinMatching.Parameters.Add(new SqlParameter("@cardNumber", cardNumber));
//            checkCardAndPinMatching.Parameters.Add(new SqlParameter("@cardPin", cardPin));
//            checkCardAndPinMatching.Transaction = tran;
//            SqlDataReader resultTable = checkCardAndPinMatching.ExecuteReader();
            
//            resultTable.Read();
//            bool doesMatch = (bool)resultTable[0];
//            resultTable.Close();
//            return doesMatch;
//        }

        static bool IsThereEnoughCash(SqlDataReader reader, decimal cashToWithdraw)
        {
            decimal availableSum = (decimal)reader["CardCash"];
            if (availableSum - cashToWithdraw < 0)
            {
                return false;
            }
            return true;
        }

        static void UpdateAccountSum(SqlConnection connection, SqlTransaction tran, string cardNumber, decimal sumToWithdraw)
        {
            string updateSum = @"UPDATE CardAccounts
                                SET CardCash =CardCash- @sumToWithdraw
                                WHERE CardNumber = @cardNumber";
            SqlCommand updateCommand = new SqlCommand(updateSum, connection);
            updateCommand.Parameters.Add(new SqlParameter("@cardNumber", cardNumber));
            updateCommand.Parameters.Add(new SqlParameter("@sumToWithdraw", sumToWithdraw));
            updateCommand.Transaction = tran;
            updateCommand.ExecuteNonQuery();
        }

        static void PrintLogsForCard(SqlConnection connection,string cardNumber, string cardPin)
        {
            SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.RepeatableRead);
            try
            {
                SqlDataReader reader = null;
                if(IsCardNumberAndPinValid(connection , transaction , cardNumber,cardPin, out reader))
                {
                    reader.Close();
                    string selectLogsForCardNumber = @"SELECT * FROM AccountTransactions
                                                       WHERE CardNumber = @cardNumber";
                    SqlCommand selectLogsForCardNumberCommand = new SqlCommand(selectLogsForCardNumber, connection);
                    selectLogsForCardNumberCommand.Parameters.Add(new SqlParameter("@cardNumber", cardNumber));
                    selectLogsForCardNumberCommand.Transaction = transaction;
                    reader = selectLogsForCardNumberCommand.ExecuteReader();
                    using (reader)
                    {
                        Console.WriteLine("Card Number:{0}",cardNumber);
                        while (reader.Read())
                        {
                            Console.WriteLine("Transaction Date:{0}     Amount:{1}",reader["TransactionDate"],reader["Amount"]);
                        }
                    }
                }
                else
                {
                    Console.WriteLine("Authentication failed.");
                }
            }
            catch (Exception e)
            {
                transaction.Rollback();
                Console.WriteLine("PrintingLogsForCard transaction failed:{0}",e.Message);
                return;
            }
            transaction.Commit();
        }

        static void Main(string[] args)
        {
            SqlConnection connection = OpenDatabaseConnection("ATM");
            connection.Open();
            using (connection)
            {
                Widthdraw(connection, 250, "1234567892","2222");
                PrintLogsForCard(connection, "1234567892", "2222");
            }
        }
    }
}
